﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_vacation_cancel_order')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_vacation_cancel_order

GO


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-23 23:52:33
-- Database:    [scm_main-hysj2]
-- Description: 假期管理-按单号取消假期生成明细记录
-- =============================================

CREATE PROCEDURE bd_proc_vacation_cancel_order
  @orderId  BIGINT,
  @userIds  VARCHAR(MAX), 
  @operateId BIGINT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #cancel
  (
  	user_serial BIGINT,
    duration  INT DEFAULT 0,
    original_qty INT DEFAULT 0,
    ht_id  INT DEFAULT 0   --时间银行记录ID
  )

  DECLARE @leaveType VARCHAR(50), @hasQuota INT

  SELECT @leaveType = leave_id FROM bd_leave_order WHERE order_id = @orderId

  --是否有额度，没有额度的假类不用产生明细数据
  SELECT @hasQuota = ISNULL(ishaveedu, 0) FROM kt_qingj WHERE bh = @leaveType

  IF @hasQuota = 0 
  BEGIN  
  	RETURN 
  END

  --10001	年休假
  --10002	调休假
  --10018	授乳假

  DECLARE @cancelType INT = 0

  IF @leaveType = '10001' 
  BEGIN  
    	SET @cancelType = 4
  END
  ELSE IF @leaveType = '10002' 
  BEGIN  
      SET @cancelType = 11
  END
  ELSE IF @leaveType = '10018' 
  BEGIN  
      SET @cancelType = 22
  END
       
  
  DECLARE @sql VARCHAR(MAX) = ''

  
  SELECT @sql ='
    INSERT INTO #cancel (user_serial, duration)
    SELECT user_serial, SUM(jsjg) FROM lr_kqingj WHERE order_id = ' + CAST(@orderId AS VARCHAR) --+ ' AND whether_validity = 1 '
  
  IF ISNULL(@userIds , '') != ''
  BEGIN  
  	SELECT @sql += ' AND user_serial IN (' + @userIds + ')'
  END
  SELECT @sql += ' GROUP BY user_serial'
  
  EXEC(@sql)

      --假期类型
      --4：年假取消

      --11：调休取消

      --22：授乳假取消

  --未扣除前
  UPDATE v SET original_qty = CASE @cancelType WHEN 4 THEN ht.vacation_annual WHEN 11 THEN ht.rest_total WHEN 22 THEN ht.vacation_lactational ELSE 0 END,
    ht_id = ht.id
  FROM #cancel v
  INNER JOIN (SELECT * FROM bd_view_kq_holidays_time_rowno WHERE row_no = 1) ht ON ht.user_serial = v.user_serial


  BEGIN TRY
  	BEGIN TRANSACTION
      IF ISNULL(@cancelType, 0) = 4
      BEGIN
        --返还年假
        UPDATE ht
        SET vacation_annual = vacation_annual + v.duration
        FROM bd_kq_holidays_time ht
        INNER JOIN #cancel v ON v.user_serial = ht.user_serial AND v.ht_id = ht.id

      END
      ELSE
    
      IF ISNULL(@cancelType, 0) = 11
      BEGIN
        --返还调休
        UPDATE ht
        SET rest_total = ht.rest_total + v.duration
        FROM bd_kq_holidays_time ht
        INNER JOIN #cancel v ON v.user_serial = ht.user_serial  AND v.ht_id = ht.id
 
      END
      ELSE
      BEGIN
        --扣除授乳假
        UPDATE ht
        SET vacation_lactational = ht.vacation_lactational + v.duration
        FROM bd_kq_holidays_time ht
        INNER JOIN #cancel v ON v.user_serial = ht.user_serial  AND v.ht_id = ht.id
 
      END

      DECLARE @now DATETIME = GETDATE()

      --生成明细数据
      INSERT INTO dbo.bd_kq_holidays(user_serial
      , dept_serial
      , dept_name
      , ho_year
      , ho_month
      , ho_date
      , ho_type
      , deduct_before
      , deduct_usage
      , deduct_remain
      , related_id
      , create_id
      , create_time)
      SELECT v.user_serial, u.user_dep, u.dep_name, YEAR(@now), MONTH(@now), @now, @cancelType, 
        original_qty, duration, original_qty + duration, @orderId, @operateId, GETDATE()
      FROM #cancel v
      INNER JOIN bd_view_user_all u ON u.user_serial = v.user_serial  		
  	COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
      RAISERROR (@errStr, 16, 1);
    END;
  END CATCH;


  DROP TABLE #cancel

  SET NOCOUNT OFF;
END